﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace iHub
{
    class PayrollManager
    {
        private dbConnector db = new dbConnector();
        private SqlDataReader read;
        private SqlCommand com;

        public void CreateVoucher(ArrayList assigns, int x, int casha)
        {
            ArrayList assignments = assigns;
            int id = x;
            float gross = 0;
            float net = 0;
            float advances = 0;
            int cash = casha;
            float tax = (float)0.02;
            int pid = 0;
            String code = "WC120";
            DateTime date = DateTime.Now;
            String d = date.ToShortDateString();

            db.connect();

            for (int i = 0; i < assignments.Count; i++)
            {
                com = new SqlCommand("select payment from Assignment_Type where type = (select type from Assignment where assign_id = "+assignments[i]+")", db.getCon());
                read = com.ExecuteReader();

                if (read.Read())
                {
                    gross += Convert.ToInt32(read["payment"]);
                }

                read.Close();
            }

            advances = gross * tax;
            net = gross - (advances+cash);

            com = new SqlCommand("dbo.createPay", db.getCon());
            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.Add(new SqlParameter("e_id", id));
            com.Parameters.Add(new SqlParameter("gross", gross));
            com.Parameters.Add(new SqlParameter("cash", cash));
            com.Parameters.Add(new SqlParameter("tax", advances));
            com.Parameters.Add(new SqlParameter("net", net));
            com.Parameters.Add(new SqlParameter("code", code));
            com.Parameters.Add(new SqlParameter("date", d));

            com.ExecuteNonQuery();

            com = new SqlCommand("select p_id from Payroll where e_id = "+id+" and p_date = '"+d+"'", db.getCon());
            read = com.ExecuteReader();

            if (read.Read())
            {
                pid = Convert.ToInt32(read["p_id"]);
            }

            read.Close();

            for(int i = 0; i < assignments.Count; i++)
            {
                com = new SqlCommand("insert into Payroll_Assignment (p_id, assign_id) values (@pid, @assignid)", db.getCon());

                com.Parameters.Add(new SqlParameter("pid", pid));
                com.Parameters.Add(new SqlParameter("assignid", Convert.ToInt32(assignments[i])));

                com.ExecuteNonQuery();
            }

            db.disconnect();
        }

        public bool checkPay(int eid)
        {
            DateTime d = DateTime.Now;
            DateTime d2 = new DateTime();
            int id = eid;
            int span = 0;
            bool check = false;

            db.connect();

            com = new SqlCommand("select top 1 p_date from Payroll where e_id = "+id+" order by p_date desc", db.getCon());
            read = com.ExecuteReader();

            if (read.Read())
            {
                d2 = Convert.ToDateTime(read["p_date"]);
            }

            if (d2 == null)
            {
                check = true;
            }
            else if (d2 != null)
            {
                span = (int)(d - d2).TotalDays;
            }

            if (span > 6)
            {
                check = true;
            }
            else
            {
                check = false;
            }

            db.disconnect();

            return check;
        }

        public DataTable viewVoucher(int i, DateTime date)
        {
            DataTable vouchers = new DataTable();
            String d = date.ToShortDateString();
            int id = i;
            String name = "";
            String tin = "";
            String address = "";
            float gross1 = 0;
            float net1=0;
            float cash=0;
            float tax = 0;
            double tax3 = 0;
            double net3 = 0;
            int pid = 0;
            String tax1 = "";
            int pay = 0;
            String location = "";
            String net = "";
            ArrayList l = new ArrayList();

            db.connect();

            vouchers.Columns.Add(new DataColumn("Name", typeof(string)));
            vouchers.Columns.Add(new DataColumn("Tax Identification Number", typeof(string)));
            vouchers.Columns.Add(new DataColumn("Employee Address", typeof(string)));
            vouchers.Columns.Add(new DataColumn("Assignment Location", typeof(string)));
            vouchers.Columns.Add(new DataColumn("Assignment Pay", typeof(string)));
            vouchers.Columns.Add(new DataColumn("Gross Amount Payable", typeof(float)));
            vouchers.Columns.Add(new DataColumn("Cash Advances", typeof(float)));
            vouchers.Columns.Add(new DataColumn("Withholding Tax", typeof(float)));
            vouchers.Columns.Add(new DataColumn("Net Amount Payable", typeof(float)));

            com = new SqlCommand("select Employee.e_name as name, Employee.tin as tin, Employee.e_address as address, Payroll.* from Payroll inner join Employee on Employee.e_id=Payroll.e_id where Payroll.e_id = "+id+" and p_date = '"+d+"'", db.getCon());
            read = com.ExecuteReader();

            if (read.Read())
            {
                name = Convert.ToString(read["name"]);
                tin = Convert.ToString(read["tin"]);
                address = Convert.ToString(read["address"]);
                pid = Convert.ToInt32(read["p_id"]);
                cash = Convert.ToInt32(read["cashadvances"]);
                gross1 = Convert.ToInt32(read["grossamountpayable"]);
                tax1 = Convert.ToString(read["withholdingtax"]);
                tax3 = Convert.ToDouble(tax1);
                tax = (float)tax3;
                net = Convert.ToString(read["netamountpayable"]);
                net3 = Convert.ToDouble(net);
                net1 = (float)net3;
            }

            read.Close();

            com = new SqlCommand("select Assignment.city as loc, Assignment_Type.payment as pay from (Assignment inner join Payroll_Assignment on Assignment.assign_id=Payroll_Assignment.assign_id) inner join Assignment_Type on Assignment.type=Assignment_Type.type where Payroll_Assignment.p_id = " + pid, db.getCon());
            read = com.ExecuteReader();

            while (read.Read())
            {
                location = Convert.ToString(read["loc"]);
                pay = Convert.ToInt32(read["pay"]);
                vouchers.Rows.Add(name, tin, address, location, pay, gross1, cash, tax, net1);
            }

            read.Close();

            db.disconnect();
            return vouchers;
        }

        public DataTable viewList(DateTime a, DateTime b)
        {
            DataTable list = new DataTable();
            string name = "";
            String temptin = "";
            string gross = "";
            double gross3 = 0;
            float gross2 = 0;
            string cash = "";
            double cash3 = 0;
            float cash2 = 0;
            string tax = "";
            double tax3 = 0;
            float tax2 = 0;
            string net = "";
            double net3 = 0;
            float net2 = 0;

            db.connect();

            list.Columns.Add(new DataColumn("Name", typeof(string)));
            list.Columns.Add(new DataColumn("Tax Identification Number", typeof(string)));
            list.Columns.Add(new DataColumn("Gross Amount Payable", typeof(float)));
            list.Columns.Add(new DataColumn("Cash Advances", typeof(float)));
            list.Columns.Add(new DataColumn("Withholding Tax", typeof(float)));
            list.Columns.Add(new DataColumn("Net Amount Payable", typeof(float)));

            com = new SqlCommand("select Employee.e_name as name, Employee.tin as tin, Payroll.* from Payroll inner join Employee on Employee.e_id=Payroll.e_id where p_date between '"+a+"' and '"+b+"'", db.getCon());
            read = com.ExecuteReader();

            while (read.Read())
            {
                name = (string)read["name"];
                temptin = (string)read["tin"];
                gross = Convert.ToString(read["grossamountpayable"]);
                gross3 = Convert.ToDouble(gross);
                gross2 = (float)gross3;
                cash = Convert.ToString(read["cashadvances"]);
                cash3 = Convert.ToDouble(cash);
                cash2 = (float)cash3;
                tax = Convert.ToString(read["withholdingtax"]);
                tax3 = Convert.ToDouble(tax);
                tax2 = (float)tax3;
                net = Convert.ToString(read["netamountpayable"]);
                net3 = Convert.ToDouble(net);
                net2 = (float)net3;

                list.Rows.Add(name, temptin, gross2, cash2, tax2, net2);
            }

            read.Close();

            db.disconnect();
            return list;
        }

        public DataTable viewBIR(int month)
        {
            int m = month;
            String m2 = "";
            DataTable list = new DataTable();
            string name = "";
            String temptin = "";
            string gross = "";
            double gross3 = 0;
            float gross2 = 0;
            string cash = "";
            double cash3 = 0;
            float cash2 = 0;
            string tax = "";
            double tax3 = 0;
            float tax2 = 0;
            string net = "";
            double net3 = 0;
            float net2 = 0;
            string code = "";
            string services = "Contractual Services";

            if (m == 1)
            {
                m2 = "January";
            }
            else if (m == 2)
            {
                m2 = "February";
            }
            else if (m == 3)
            {
                m2 = "March";
            }
            else if (m == 4)
            {
                m2 = "April";
            }
            else if (m == 5)
            {
                m2 = "May";
            }
            else if (m == 6)
            {
                m2 = "June";
            }
            else if (m == 7)
            {
                m2 = "July";
            }
            else if (m == 8)
            {
                m2 = "August";
            }
            else if (m == 9)
            {
                m2 = "September";
            }
            else if (m == 10)
            {
                m2 = "October";
            }
            else if (m == 11)
            {
                m2 = "November";
            }
            else if (m == 12)
            {
                m2 = "December";
            }

            db.connect();

            list.Columns.Add(new DataColumn("Month", typeof(string)));
            list.Columns.Add(new DataColumn("Tax Identification Number", typeof(string)));
            list.Columns.Add(new DataColumn("Name", typeof(string)));
            list.Columns.Add(new DataColumn("ATC Code", typeof(string)));
            list.Columns.Add(new DataColumn("Nature of Payment", typeof(string)));
            list.Columns.Add(new DataColumn("Gross Amount Payable", typeof(float)));
            list.Columns.Add(new DataColumn("Cash Advances", typeof(float)));
            list.Columns.Add(new DataColumn("Withholding Tax", typeof(float)));
            list.Columns.Add(new DataColumn("Net Amount Payable", typeof(float)));

            com = new SqlCommand("select Employee.e_name as name, Employee.tin as tin, Payroll.* from Payroll inner join Employee on Employee.e_id=Payroll.e_id where month(p_date) = " + m, db.getCon());
            read = com.ExecuteReader();

            while (read.Read())
            {
                name = (string)read["name"];
                temptin = (string)read["tin"];
                code = Convert.ToString(read["atc_code"]);
                gross = Convert.ToString(read["grossamountpayable"]);
                gross3 = Convert.ToDouble(gross);
                gross2 = (float)gross3;
                cash = Convert.ToString(read["cashadvances"]);
                cash3 = Convert.ToDouble(cash);
                cash2 = (float)cash3;
                tax = Convert.ToString(read["withholdingtax"]);
                tax3 = Convert.ToDouble(tax);
                tax2 = (float)tax3;
                net = Convert.ToString(read["netamountpayable"]);
                net3 = Convert.ToDouble(net);
                net2 = (float)net3;

                list.Rows.Add(m2, temptin, name, code, services, gross2, cash2, tax2, net2);
            }

            read.Close();

            db.disconnect();
            return list;
        }
    }
}
